﻿'Description:齐套统计分析
'Copyright (c) : 通力凯顿（北京）系统集成有限公司
'Writer:longb
'create Date:2022-06-07
'Modify by longb
'Rewriter:
'Rewrite Date:
Imports BusinessRule.IBusiness
Imports System.Text
Imports BusinessRule.DTO
Imports DatabaseAccess.DBUtility
Imports DatabaseAccess.IDAL
Imports DatabaseAccess.DALFactory
Imports System.Data.OracleClient
Public Class FullStatisticsAnalysisBusiness
    Private m_ContainerObj As New uLMSContainerBusiness
    ''' <summary>
    ''' 获取任务单信息
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetTaskOrderData(ByVal para As Dictionary(Of String, String)) As DataTable
        Dim StrSql As String = GetTaskOrdersql(para)
        Return DatabaseHelper.Query_ReturnDatTable(StrSql)
    End Function
    ''' <summary>
    ''' (分页)获取任务单信息
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetTaskOrderDataByPagesize(ByVal para As Dictionary(Of String, String)) As uMESProcessResult
        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex").ToString)
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = GetTaskOrdersql(para)
        Return DatabaseHelper.RunProcedureForPage(paraPage)
    End Function

    ''' <summary>
    ''' 获取任务单信息sql
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetTaskOrdersql(ByVal para As Dictionary(Of String, String)) As String
        Dim sb As New StringBuilder
        sb.AppendLine(<string>
                           SELECT wf.description wfdesc,u.attributevalue,e.containerid,e.containername,mfg.mfgordername,pb.productname,p.description, 
                 wfb.workflowname,e.containerno,e.containerno togetherflag,e.mfgorderid,e.BIGSMALLBOMID bomid,PB2.productname MPRODUCTNAME,
                 decode(e.containerid,null,'未生成','已生成') TaskBillStatus,e.workflowid,
                 wfs.workflowstepname,wfs.sequence,to_char(e.plannedstartdate,'yyyy-MM-dd HH:mi') plannedstartdate, 
                to_char(e.plannedfinishdate,'yyyy-MM-dd HH:mi') plannedfinishdate,
                decode(WF.WORKFLOWTYPE,'施工工艺','临时','技通工艺','临时','正常') WORKFLOWTYPE,NVL(e.ICFULLSETRATE,0) QTL,fulltime,0 STEPCOUNT,0 QT
                FROM enginepartplan e 
                INNER JOIN workflowstep wfs ON e.workflowstepid=wfs.workflowstepid
                 INNER JOIN mfgorder mfg ON mfg.mfgorderid=e.mfgorderid AND mfg.productid != e.productid
                 LEFT JOIN Userattribute u ON u.parentid=mfg.mfgorderid AND u.userattributename='发动机'
                 LEFT JOIN product p ON e.productid=p.productid
                 LEFT JOIN productbase pb ON p.productbaseid=pb.productbaseid
                 LEFT JOIN workflow wf ON wf.workflowid=e.workflowid
                 left join workflowbase wfb on wfb.workflowbaseid=wf.workflowbaseid
LEFT JOIN PRODUCTBASE PB1  ON PB1.PRODUCTBASEID = mfg.PRODUCTBASEID
LEFT JOIN PRODUCT P1  ON P1.PRODUCTID = NVL(PB1.REVOFRCDID, mfg.PRODUCTID) 
LEFT JOIN PRODUCTBASE PB2  ON PB2.PRODUCTBASEID = P1.PRODUCTBASEID
                 LEFT JOIN (SELECT COUNT(b.Bomid) countnum,wm_concat(bt.billtypename) billtypename,
                to_char(wm_concat(DECODE(bt.billtypename,'大小件目录',b.bomid,NULL))) bomid,b.workflowid 
                from BOM b,billtype bt WHERE b.billtypeid=bt.billtypeid GROUP BY b.workflowid
                ) b ON b.workflowid=e.workflowid
                LEFT JOIN(
 			     	SELECT MAX(pm.EXPECTMATERIALDATE) FULLTIME,im.containerNo  FROM issueerpmaterial im   
						LEFT JOIN partmenuinfo pm ON im.partmenuinfoid=pm.partmenuinfoid GROUP BY im.containerNo
 			     ) ma ON ma.containerNo=e.containerNo
                      </string>)
        '            LEFT JOIN (
        '               SELECT COUNT(WORKFLOWSTEPID) STEPCOUNT,WORKFLOWID,CONTAINERNO FROM (
        '	SELECT  DISTINCT ws.WORKFLOWSTEPID,WORKFLOWID,CONTAINERNO FROM WORKFLOWSTEP WS 
        '	LEFT JOIN specbase sb ON sb.specbaseid=ws.specbaseid
        '	LEFT JOIN spec s ON s.specid=NVL(sb.revofrcdid,ws.specid)
        '	LEFT JOIN PARTMENUINFO pm ON pm.SPECID =s.SPECID
        '	WHERE  NVL(pm.PARTMENUINFOID,0)>'0'
        '	)
        '	GROUP BY WORKFLOWID,CONTAINERNO
        '           ) wfw ON wfw.WORKFLOWID =e.WORKFLOWID AND wfw.containerNo=e.containerNo
        '    LEFT JOIN (
        '    SELECT COUNT(DECODE((CASE WHEN PCOUNT!=0 AND (PCOUNT=CCOUNT) THEN 0 ELSE 1 END),0,1,NULL)) QT,containerNo FROM(
        'SELECT COUNT(1)  PCOUNT,COUNT(DECODE((CASE WHEN CONFIRMQTY>0 THEN 1 ELSE 0 END),0,1,NULL)) CCOUNT, SPECID,containerNo FROM (
        '	SELECT pm.SPECID,im.WORKFLOWSTEPID,pm.firstassembleqty-nvl(SUM(con.CONFIRMQTY),0) CONFIRMQTY,p.description,p.productid,im.containerNo
        '	FROM issueerpmaterial im   
        '	LEFT JOIN partmenuinfo pm ON im.partmenuinfoid=pm.partmenuinfoid
        '	LEFT JOIN product p ON p.productid=pm.productid
        '	LEFT JOIN (SELECT SUM(CONFIRMQTY)CONFIRMQTY,ISSUEERPMATERIALID FROM issueerpmaterialconfirmation GROUP BY ISSUEERPMATERIALID) con ON con.ISSUEERPMATERIALID=im.ISSUEERPMATERIALID                      
        ' where  im.ischange='0' GROUP BY pm.firstassembleqty,pm.SPECID,im.WORKFLOWSTEPID,p.description,p.productid,im.containerNo ) GROUP BY SPECID,containerNo
        ' ) GROUP BY containerNo
        '    ) sdd ON sdd.containerNo=e.containerNo


        sb.AppendLine(" WHERE e.containerno is not null AND b.bomid >'0' and INSTR(b.billtypename,'大小件目录')>0 ")

        'add by longb 2024-04-10
        If para.ContainsKey("usecompleted") Then
            If Not String.IsNullOrWhiteSpace(para("usecompleted")) Then
                sb.AppendFormat(" and e.usecompleted='{0}' ", para("usecompleted"))
            End If
        End If


        If para.ContainsKey("ContainerNo") Then
            If Not String.IsNullOrWhiteSpace(para("ContainerNo")) Then
                sb.AppendFormat(" and e.ContainerNo like '%{0}%' ", para("ContainerNo"))
            End If
        End If

        If para.ContainsKey("Machine") Then
            If Not String.IsNullOrWhiteSpace(para("Machine")) Then
                sb.AppendFormat(" and u.attributevalue like '%{0}%' ", para("Machine"))
            End If
        End If

        If para.ContainsKey("MfgOrdername") Then
            If Not String.IsNullOrWhiteSpace(para("MfgOrdername")) Then
                sb.AppendFormat(" and mfg.mfgordername like '%{0}%' ", para("MfgOrdername"))
            End If
        End If
        If para.ContainsKey("Status") Then
            If para("Status") = "0" Then
                sb.AppendLine(" AND decode(e.containerid,null,'未生成','已生成')='未生成' ")
            ElseIf para("Status") = "1" Then
                sb.AppendLine(" AND decode(e.containerid,null,'未生成','已生成')='已生成' ")
            Else

            End If
        End If

        If para.ContainsKey("WORKFLOWTYPE") Then
            sb.AppendFormat(" AND decode(WF.WORKFLOWTYPE,'施工工艺','临时','技通工艺','临时','正常')='{0}' ", para("WORKFLOWTYPE"))
        End If


        sb.AppendLine("Order by e.plannedfinishdate,wfs.sequence,mfg.mfgordername,pb.productname,e.containername ASC")
        Return sb.ToString
        'Return DatabaseHelper.Query_ReturnDatTable(sb.ToString)
    End Function
    ''' <summary>
    ''' 查询大小件齐套率
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetBigSmallData(ByVal para As Dictionary(Of String, String)) As DataTable
        Dim strFirstSecondIssue As String = m_ContainerObj.CheckFirstSecondIssueNew(para("ContainerID")) '判断是初装（1）、检装（2）
        Dim qty As String = "pm.firstassembleqty"
        If strFirstSecondIssue = "2" Then
            qty = "pm.checkassembleqty "
        End If
        Dim sb As New StringBuilder
        sb.AppendFormat(<string>SELECT fulltime,partmenuinfoid,firstassembleqty,containerno,productname,description,parttype,SUM(CONFIRMQTY)CONFIRMQTY FROM (
                select pm.EXPECTMATERIALDATE fulltime,pm.partmenuinfoid,{0} firstassembleqty,im.containerno,im.containerid, cm.productname,p.description,im.issueqty
                ,isc.lossqty,im.issueqty-nvl(isc.lossqty,0) spanQty,cm.productbatchno,pm.parttype,im.issueerpmaterialId,nvl(con.CONFIRMQTY,0)CONFIRMQTY
                FROM issueerpmaterial im                
                LEFT JOIN Collecterpmaterial cm ON im.collecterpmaterialid=cm.collecterpmaterialid
                LEFT JOIN partmenuinfo pm ON im.partmenuinfoid=pm.partmenuinfoid               
                LEFT JOIN product p ON p.productid=pm.productid
                LEFT JOIN ENGINEPARTPLAN ep ON ep.CONTAINERID =im.CONTAINERID  
                LEFT JOIN (SELECT SUM(CONFIRMQTY)CONFIRMQTY,ISSUEERPMATERIALID FROM issueerpmaterialconfirmation where mfgorderid='{1}' GROUP BY ISSUEERPMATERIALID) con ON con.ISSUEERPMATERIALID=im.ISSUEERPMATERIALID
                LEFT JOIN (SELECT isc.issueerpmaterialid,sum(isc.lossqty) lossqty FROM issueerpmaterialscrapinfo isc group by isc.issueerpmaterialid) isc
                        on isc.issueerpmaterialid=im.issueerpmaterialid</string>.Value, qty, para("mfgorderid"))
        sb.AppendFormat(" where im.containerNo='{0}' and im.ischange='0'", para("ContainerNo"))
        If para.ContainsKey("specid") Then
            If Not String.IsNullOrWhiteSpace(para("specid")) Then
                sb.AppendFormat(" and pm.specid= '{0}' ", para("specid"))
            End If
        End If
        If para.ContainsKey("workflowid") Then 'add by longb 2023-03-30 传工艺ID--规避（不同订单或不同工艺，（相同发动机号部件--生成任务单号相同））情况
            If Not String.IsNullOrWhiteSpace(para("workflowid")) Then
                sb.AppendFormat(" and ep.WORKFLOWID= '{0}' ", para("workflowid"))
            End If
        End If
        If strFirstSecondIssue = "2" Then 'add by longb 检装阶段只显示大于0 的检装大小件
            sb.AppendLine(" AND nvl(pm.checkassembleqty,0)>0")
        End If
        sb.AppendFormat(" ) GROUP BY fulltime,partmenuinfoid,firstassembleqty,containerno,productname,description,parttype ORDER BY productname ASC")
        Return DatabaseHelper.Query_ReturnDatTable(sb.ToString)
    End Function
    ''' <summary>
    ''' 查询工序齐套率
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetSpec(ByVal para As Dictionary(Of String, String)) As DataTable
        Dim strFirstSecondIssue As String = m_ContainerObj.CheckFirstSecondIssueNew(para("ContainerID")) '判断是初装（1）、检装（2）
        Dim qty As String = "pm.firstassembleqty"
        If strFirstSecondIssue = "2" Then
            qty = "pm.checkassembleqty "
        End If
        Dim sb As New StringBuilder
        sb.AppendLine(<string>
                     SELECT WS.WORKFLOWSTEPID,WS.WORKFLOWSTEPNAME,WS.SEQUENCE,s.OPDesc,S.SPECNO,s.SPECID,to_char(PLANNEDSTARTDATE,'yyyy-MM-dd hh24:mi') PLANNEDSTARTDATE,to_char(PLANNEDFINISHDATE,'yyyy-MM-dd hh24:mi') PLANNEDFINISHDATE,NVL(PCOUNT,0)PCOUNT,NVL(CCOUNT,0)CCOUNT,'' fulltime
                    FROM  WORKFLOWSTEP WS 
                    LEFT JOIN specbase sb ON sb.specbaseid=ws.specbaseid
                    LEFT JOIN spec s ON s.specid=NVL(sb.revofrcdid,ws.specid)
                    LEFT JOIN (
                    SELECT MIN(CSD.PLANNEDSTARTDATE) PLANNEDSTARTDATE,MAX(CSD.PLANNEDFINISHDATE) PLANNEDFINISHDATE,CSD.WORKFLOWSTEPID FROM</string>.Value)
        sb.AppendFormat(" CONTAINERSTEPDISPATCH CSD WHERE CSD.ISUSE = 0 AND CSD.CONTAINERID='{0}'", para("ContainerID"))
        sb.AppendLine(" GROUP BY CSD.WORKFLOWSTEPID )CSD ON WS.WORKFLOWSTEPID = CSD.WORKFLOWSTEPID")
        sb.AppendLine("LEFT JOIN (SELECT COUNT(1)  PCOUNT,COUNT(DECODE((CASE WHEN CONFIRMQTY<=0 THEN 0 ELSE 1 END),0,1,NULL)) CCOUNT, SPECID FROM (")
        sb.AppendFormat(<string>SELECT pm.SPECID,im.WORKFLOWSTEPID,{0}-nvl(SUM(con.CONFIRMQTY),0) CONFIRMQTY,p.description,p.productid
						FROM  issueerpmaterial im
						LEFT JOIN partmenuinfo pm  ON im.partmenuinfoid=pm.partmenuinfoid
						LEFT JOIN product p ON p.productid=pm.productid
						LEFT JOIN (SELECT SUM(CONFIRMQTY)CONFIRMQTY,ISSUEERPMATERIALID FROM issueerpmaterialconfirmation  where mfgorderid='{1}' GROUP BY ISSUEERPMATERIALID) con ON con.ISSUEERPMATERIALID=im.ISSUEERPMATERIALID
                      </string>.Value, qty, para("mfgorderid"))
        sb.AppendFormat(" where im.containerNo='{0}' and im.ischange='0'", para("ContainerNo"))
        If strFirstSecondIssue = "2" Then
            sb.AppendLine(" AND nvl(pm.checkassembleqty,0)>0")
        End If
        sb.AppendFormat(" GROUP BY {0},pm.SPECID,im.WORKFLOWSTEPID,p.description,p.productid ) GROUP BY SPECID", qty)
        sb.AppendLine(" ) spe ON spe.SPECID=s.SPECID")
        sb.AppendFormat(" WHERE  WS.WORKFLOWID='{0}' ", para("workflowid"))
        sb.AppendLine(" ORDER BY WS.SEQUENCE")
        Return DatabaseHelper.Query_ReturnDatTable(sb.ToString)
    End Function
    ''' <summary>
    ''' 设置大小件--预计齐套时间
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function SetBigSmallFullTime(ByVal para As Dictionary(Of String, String)) As Boolean
        Dim StrSql As New StringBuilder
        StrSql.AppendFormat("UPDATE partmenuinfo SET EXPECTMATERIALDATE='{0}' WHERE partmenuinfoid='{1}'", para("time"), para("partmenuinfoid"))
        Return DatabaseHelper.ExecuteSql(StrSql.ToString) > 0
    End Function

End Class
